package com.ybkj.o2o.mc.controller.web;

import com.ybkj.o2o.mc.common.DataForm;
import com.ybkj.o2o.mc.entity.Passenger;
import com.ybkj.o2o.mc.entity.PassengerGrade;
import com.ybkj.o2o.mc.manager.PassengerGradeManager;
import com.ybkj.o2o.mc.manager.PassengerImportManager;
import com.ybkj.o2o.mc.manager.PassengerManager;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RequestPart;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.util.WebUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;

/**
 * Created by shine on 15/8/19.
 */
@RestController
@RequestMapping("passengerImport")
public class PassengerImportController {

  @Autowired
  private PassengerImportManager passengerImportManager;

  @Autowired
  private PassengerGradeManager passengerGradeManager;

  @Autowired
  private PassengerManager passengerManager;

  /**
   * 导入远程数据库到本数据库中.
   *
   * @param server   数据库地址
   * @param dbname   数据库名
   * @param user     用户名
   * @param password 密码
   * @param appkey   系统key
   */
  @RequestMapping(value = {"startImport"}, method = RequestMethod.POST)
  public DataForm<Map<String, Object>> startImport(@RequestParam("server") String server,
                                                   @RequestParam("dbname") String dbname,
                                                   @RequestParam("user") String user,
                                                   @RequestParam("password") String password,
                                                   @RequestParam("appkey") String appkey) {

    return passengerImportManager.startImport(server, dbname, user, password, appkey);

  }
  
  /**
   * 从excel中导入数据到本地数据库中.
   *
   * @param req         请求
   * @param excelFile   导入的文件
   * @param companyId   公司主键
   * @param companyName 公司名称
   * @param gradeId     等级主键
   * @param appKey      系统key
   */
  @RequestMapping(value = {"startExcelImport"}, method = RequestMethod.POST)
  public Map<String, String> startExcelImport(
          HttpServletRequest req,
          @RequestPart("excelFile") MultipartFile excelFile,
          @RequestParam("companyId") Long companyId,
          @RequestParam("companyName") String companyName,
          @RequestParam("gradeId") Long gradeId,
          @RequestParam("appKey") String appKey) throws IOException {

    String bathPath = getBasePath(req);

    File dir = new File(bathPath, "upload/excelImport");

    FileUtils.forceMkdir(dir);

    String uid = UUID.randomUUID().toString().replaceAll("-", "");

    String fileName = String.format("%s.xls", uid);

    File file = new File(dir, fileName);

    byte[] data = excelFile.getBytes();

    FileOutputStream out = new FileOutputStream(file);

    IOUtils.write(data, out);
    IOUtils.closeQuietly(out);

    InputStream is = new FileInputStream(file);
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);


    PassengerGrade passengerGrade = passengerGradeManager.fetch(gradeId, companyId, appKey);

    int totalCount = 0;
    int successCount = 0;
    int failureCount = 0;
    String message = "";

    // 循环工作表Sheet
    for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
      HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
      if (hssfSheet == null) {
        continue;
      }
      // 循环行Row
      for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
        HSSFRow hssfRow = hssfSheet.getRow(rowNum);
        if (hssfRow == null) {
          continue;
        }

        String name = null;
        String phone = null;

        for (int cellNum = 0; cellNum <= 1; cellNum++) {

          HSSFCell cell = hssfRow.getCell(cellNum);

          if (null != cell) {
            if (cellNum == 0) {
              name = getValue(cell);
            } else if (cellNum == 1) {
              phone = getValue(cell);
            }
          }

        }

        if (StringUtils.isBlank(name)) {
          name = "先生/女士";
        }

        try {
          Passenger local = passengerManager.findByPhoneAndAppKey(phone, appKey);
          if (null == local) {
            Passenger passenger = new Passenger();
            passenger.setPhone(phone);
            passenger.setName(name);
            passenger.setAllowOverdraw(false);
            passenger.setInBlackList(false);
            passenger.setType(0);
            passenger.setGender(0);
            passenger.setAppKey(appKey);
            passenger.setDrawMoney(0.0);
            passenger.setNodrawMoney(0.0);
            passenger.setFrozenMoney(0.0);
            passenger.setReceiptMoney(0.0);
            passenger.setCompanyId(companyId);
            passenger.setCompanyName(companyName);
            passenger.setGradeId(passengerGrade.getId());
            passenger.setGradeName(passengerGrade.getName());
            passengerManager.insert(passenger);
            ++successCount;
          } else {
            ++failureCount;
            message += "手机号" + phone + "已存在<br/>";
          }
        } catch (Exception ex) {
          ++failureCount;
          ex.printStackTrace();
        }
        ++totalCount;
      }

    }

    if (null != is) {
      is.close();
    }

    if (file.exists()) {
      FileUtils.deleteQuietly(file);
    }

    Map<String, String> map = new HashMap<>();
    map.put("totalCount", String.valueOf(totalCount));
    map.put("successCount", String.valueOf(successCount));
    map.put("failureCount", String.valueOf(failureCount));
    map.put("message", message);

    return map;

  }

  /**
   * 获取excel中cell解析出的数据.
   *
   * @param hssfCell 单元格
   * @return 解析出的数据
   */
  public static String getValue(HSSFCell hssfCell) {
    if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
      // 返回布尔类型的值
      return String.valueOf(hssfCell.getBooleanCellValue());
    } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
      // 返回数值类型的值
      return String.valueOf(hssfCell.getNumericCellValue());
    } else {
      // 返回字符串类型的值
      return String.valueOf(hssfCell.getStringCellValue());
    }
  }

  private String getBasePath(HttpServletRequest req) {

    try {
      return WebUtils.getRealPath(req.getSession().getServletContext(), "/");
    } catch (FileNotFoundException ex) {
      ex.printStackTrace();
    }

    return StringUtils.EMPTY;
  }

}
